BigQuery MLで予測したデータをData Portalで可視化してみる

#Google BigQuery

#Looker Studio(Google Data Portal)

じょんすみす

2022.09.25

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

こんにちは、小澤です。

BigQueryはコンソール上でSQLを実行して取得したデータを簡単にData Portalやスプレッドシートで利用しやすい仕組みが提供されています。今回は、その仕組みを利用してBigQuery MLで予測した結果を可視化してみたいと思います。

BigQueryのデータをData Portalで見る簡単な方法

BigQueryではクエリの結果を簡単にData Portalやスプレッドシートに連携する方法を用意してくれています。出力された結果の右上にある「データを探索」からどちらを利用するか選択します。

これは、スプレッドシートのコネクテッドシートやData Portalのデータソースとして、クエリの出力結果を手軽に利用する仕組みとなっています。

「データポータルで調べる」を選択すると、以下のようにクエリの結果の列ごとにフィールドが生成されたデータソースが生成されるとともに、表と棒グラフがデフォルトで作成された状態となります。

「シートで調べる」を選択した場合は、出力の結果が読み込まれたスプレッドシートが作成されます。

今回はData Portalを利用して可視化してみます。

予測モデルの作成と推論結果の付与

今回はAir Passengersデータセットを使って時系列予測をしています。このデータをCSVで取得して、BigQueryにインポート済みの状態にしています。

まずは、BigQueryのARIMA_PLUSを使ってモデルを作成します。

create or replace <dataset>.airline_arima_model
options
  (model_type = 'ARIMA_PLUS',
   time_series_timestamp_col = 'month', -- 時系列情報の列
   time_series_data_col = 'passengers', -- 予測対象の列
   auto_arima = TRUE,
   data_frequency = 'AUTO_FREQUENCY',
   decompose_time_series = TRUE
  ) AS
select 
  month,
  passengers
from
  `<project>.<dataset>.airline_passengers`

projectやdatasetはご利用の環境に合わせて具体的な値を入れてください。

これでモデルが作成されます。 BigQuery MLの時系列予測モデルは、 ml.predict 関数を使って予測ができます。

select
  *
from
  ml.forecast(
    <dataset>.airline_arima_model,
    struct(10 as horizon, 0.80 as confidence_level)
  )

horizon で予測する期間、confidence_levelで上振れと下振れの値が何パーセントの範囲とするかを指定します。

結果は以下のように表示されます。

これで、モデルの作成と推論結果の確認が完了です。

予測結果を可視化する

まずは、可視化時に利用可能な形式でデータを取得します。

-- 元のデータ
select 
  month,
  passengers,
  -- UNIONするために列情報をあわせる
  -- モデルの推論結果を入れる部分はNULLを入れる
  null as forecast_passengers,
  null as lower_bound,
  null as upper_bound
from
  `..airline_passengers`
where
  -- 可視化時に見やすくするようにデータの範囲を絞り込んでいる
  month > "1955-01-01"
-- データをUNION結合する
union all
select
  -- 推論結果の日時情報はtimestamp型なのでdate型に変換して列名をあわせる
  date(forecast_timestamp) as month,
  -- 元のデータあわせた列
  null as passengers,
  -- 推論結果のうち利用する列
  forecast_value as forecast_passengers,
  prediction_interval_lower_bound as lower_bound,
  prediction_interval_upper_bound as upper_bound
from
  ml.forecast(
    model .airline_arima_model,
    struct(10 as horizon, 0.80 as confidence_level)
  )

このクエリの結果を先ほどの仕組みを使ってData Portalで可視化します。「データポータルで調べる」を選択したのち、期間 > 時系列グラフを新規に作成します。

グラフの軸を以下のように指定します。

ディメンション
- monthを選択し、タイプを「年、月」に設定
指標
- 以下4つを指定
  - passengers
  - forcast_passengers
  - upper_bound
  - lower_bound
- 今回のデータにディメンションの重複は無いので指標は合計のままで問題ありません

また、グラフのスタイル > 全般 > 欠損データにて、「線を途切れさせる」を選択します。

この設定で以下のようなグラフが表示されます。

これで元データとそこからの時系列予測を使った可視化が出来ました。

おわりに

今回は、BigQeury MLを使って時系列予測したデータをData Portalで手軽に可視化する方法を紹介しました。サクッと可視化しながらモデルを作ってみるには非常に便利な機能ですし、それをもとに定常的に可視化する必要がある場合などには、そのまま同じSQLをデータソースとして利用することも可能なので、連携が捗りますね。